﻿using SIIE_ERP.Codigo;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SIIE_ERP.BaseDatos.Configuracion.Empresa
{
    public class dbo_Cat_Empresa_Datos_Factura
    {
        #region Propiedades

        private string _RFC;
        private string _Usuario;

        #endregion
        
        #region Constructores

        public dbo_Cat_Empresa_Datos_Factura(string RFC, string Usuario)
        {
            this._RFC = RFC;
            this._Usuario = Usuario;
        }

        #endregion
        
        #region INSERT

        public Respuesta Insert_Empresa_Datos_Factura(
            	        
	        int Id_Regimen_Fiscal,
            string Aprobacion_SICOFI,
            string Mime_Type,
            string CBB,
            string Mime_TypeLogo,
            string LogoFile,
            bool IsActiveFE,
            bool IsCompleteFE,
            int FactElection                                	        
        )
        {
            try
            {
                string Query = @"INSERT INTO dbo_Cat_Empresa_Datos_Factura
                             (
                                Id_Datos_Factura,
                                RFC,
                                Id_Regimen_Fiscal,
                                Aprobacion_SICOFI,
                                Mime_Type,
                                CBB,
                                Mime_TypeLogo,
                                LogoFile,
                                IsActiveFE,
                                IsCompleteFE,
                                FactElection,
                                Fecha_Alta,
                                Usuario_Alta,
                                Fecha_Mantto,
                                Usuario_Mantto
                             )
                             VALUES
                             (
                                IIF(DCount('*', 'dbo_Cat_Empresa_Datos_Factura') = 0, 1, DMax('Id_Datos_Factura', 'dbo_Cat_Empresa_Datos_Factura') + 1),
                                '@@RFC',
	                            @@Id_Regimen_Fiscal,
                                '@@Aprobacion_SICOFI',
                                '@@Mime_Type',
                                '@@CBB',
                                '@@Mime_TypeLogo',
                                '@@LogoFile',
                                @@IsActiveFE,
                                @@IsCompleteFE,
                                @@FactElection,                                
	                            NOW(),
                                '@@Usuario_Alta',
                                NOW(),
                                '@@Usuario_Mantto'
                             )";

                Query = Query.Replace("@@RFC", this._RFC);
                Query = Query.Replace("@@Id_Regimen_Fiscal", Id_Regimen_Fiscal.ToString());
                Query = Query.Replace("@@Aprobacion_SICOFI", Aprobacion_SICOFI);
                Query = Query.Replace("@@Mime_Type", Mime_Type);
                Query = Query.Replace("@@CBB", CBB);
                Query = Query.Replace("@@Mime_TypeLogo", Mime_TypeLogo);
                Query = Query.Replace("@@LogoFile", LogoFile);
                Query = Query.Replace("@@FactElection", FactElection.ToString());

                if (IsActiveFE)
                    Query = Query.Replace("@@IsActiveFE", "1");
                else
                    Query = Query.Replace("@@IsActiveFE", "0");

                if (IsCompleteFE)
                    Query = Query.Replace("@@IsCompleteFE", "1");
                else
                    Query = Query.Replace("@@IsCompleteFE", "0");
                
                Query = Query.Replace("@@Usuario_Alta", this._Usuario);
                Query = Query.Replace("@@Usuario_Mantto", this._Usuario);

                return SrvDB.ExecuteQuery_Insert(Query);                
            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }            
        }

        #endregion

        #region UPDATE

        public Respuesta Update_Empresa_Datos_Factura_FacturacionConvencional
        (
            byte[] cbbBytes,
            string Myme,
            string Sicofi
        )
        {
            DataTable ds_a = Get_Empresa_Datos_Factura();

            string Query = @"UPDATE dbo_Cat_Empresa_Datos_Factura SET
                                        
	                                Mime_Type = @Myme,	                             
                                    Aprobacion_SICOFI = @Aprobacion_SICOFI,   
                                    CBB = @cbbBytes,                                                                        
                                    Fecha_Mantto = NOW(),
                                    Usuario_Mantto = '@@Usuario_Mantto'

                                 WHERE Id_Datos_Factura = @@Id";

            Query = Query.Replace("@@Usuario_Mantto", this._Usuario);            
            Query = Query.Replace("@@Id", (ds_a.Rows[0]["Id_Datos_Factura"].ToString()));

            OleDbCommand command = new OleDbCommand(Query);
            command.Parameters.AddWithValue("@Myme", Myme);
            command.Parameters.AddWithValue("@Aprobacion_SICOFI", Sicofi);
            command.Parameters.AddWithValue("@cbbBytes", cbbBytes);

            return SrvDB.ExecuteQuery_Update(command);
        }

        public Respuesta Update_Empresa_Datos_Factura_LOGO
        (
            byte[] LogoBytes,
            string Myme
        )
        {
            DataTable ds_a = Get_Empresa_Datos_Factura();

            string Query = @"UPDATE dbo_Cat_Empresa_Datos_Factura SET
                                        
                                    Mime_TypeLogo = @Myme,	                                
                                    LogoFile = @Logo,                                    
                                    Fecha_Mantto = NOW(),
                                    Usuario_Mantto = '@@Usuario_Mantto'

                                 WHERE Id_Datos_Factura = @@Id";

            Query = Query.Replace("@@Usuario_Mantto", this._Usuario);            
            Query = Query.Replace("@@Id", (ds_a.Rows[0]["Id_Datos_Factura"].ToString()));

            OleDbCommand command = new OleDbCommand(Query);
            command.Parameters.AddWithValue("@Myme", Myme);
            command.Parameters.AddWithValue("@Logo", LogoBytes);

            return SrvDB.ExecuteQuery_Update(command);
        }

        public Respuesta Update_Empresa_Datos_Factura_RegimenFiscal(string Id_Regimen)
        {
            try
            {
                DataTable ds_a = Get_Empresa_Datos_Factura();

                string Query = @"UPDATE dbo_Cat_Empresa_Datos_Factura SET
                                        
	                                Id_Regimen_Fiscal = @@Id_Regimen,
                                    Fecha_Mantto = NOW(),
                                    Usuario_Mantto = '@@Usuario_Mantto'

                                 WHERE Id_Datos_Factura = @@Id";

                Query = Query.Replace("@@Usuario_Mantto", this._Usuario);
                Query = Query.Replace("@@Id_Regimen", Id_Regimen);
                Query = Query.Replace("@@Id", (ds_a.Rows[0]["Id_Datos_Factura"].ToString()));

                return SrvDB.ExecuteQuery_Update(Query);
            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }
        }

        public Respuesta Update_Empresa_Datos_Factura_Alta_FE()
        {
            try
            {
                DataTable ds_a = Get_Empresa_Datos_Factura();
                
                string Query = @"UPDATE dbo_Cat_Empresa_Datos_Factura SET
                                        
	                                IsActiveFE = 0,
                                    IsCompleteFE = 1,
                                    Fecha_Mantto = NOW(),
                                    Usuario_Mantto = '@@Usuario_Mantto'

                                 WHERE Id_Datos_Factura = @@Id";
                
                Query = Query.Replace("@@Usuario_Mantto", this._Usuario);
                Query = Query.Replace("@@Id", (ds_a.Rows[0]["Id_Datos_Factura"].ToString()));

                return SrvDB.ExecuteQuery_Update(Query);
            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }
        }

        public Respuesta Update_Empresa_Datos_Factura_Finish_FE()
        {
            try
            {
                DataTable ds_a = Get_Empresa_Datos_Factura();

                string Query = @"UPDATE dbo_Cat_Empresa_Datos_Factura SET
                                        
	                                IsActiveFE = 1,
                                    IsCompleteFE = 1,
                                    Fecha_Mantto = NOW(),
                                    Usuario_Mantto = '@@Usuario_Mantto'

                                 WHERE Id_Datos_Factura = @@Id";

                Query = Query.Replace("@@Usuario_Mantto", this._Usuario);
                Query = Query.Replace("@@Id", (ds_a.Rows[0]["Id_Datos_Factura"].ToString()));
                
                dbo_Cat_Empresa_Certificado_FE cert = new dbo_Cat_Empresa_Certificado_FE(_RFC, _Usuario);
                DataTable ds_cert = cert.Get_Empresa_Certificado_FEes();

                string Query2 = @"UPDATE dbo_Cat_Empresa_Serie SET
                                        
	                                Id_Certificado = @@Id
                                    
                                 WHERE RFC = '@@RFC'";

                Query2 = Query2.Replace("@@RFC", this._RFC);
                Query2 = Query2.Replace("@@Id", (ds_cert.Rows[0]["Id_Certificado_FE"].ToString()));
                
                SrvDB.ExecuteQuery_Update(Query);
                return SrvDB.ExecuteQuery_Update(Query2);
            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }
        }

        public Respuesta Update_Empresa_Datos_Factura_Por_Id(
            object Id,
            int Id_Regimen_Fiscal,
            string Aprobacion_SICOFI,
            string Mime_Type,
            string CBB,
            string Mime_TypeLogo,
            string LogoFile,
            bool IsActiveFE,
            bool IsCompleteFE,
            int FactElection 
        )
        {
            try
            {
                string Query = @"UPDATE dbo_Cat_Empresa_Datos_Factura SET
                                        
	                                Id_Regimen_Fiscal = @@Id_Regimen_Fiscal,
                                    Aprobacion_SICOFI = '@@Aprobacion_SICOFI',
                                    Mime_Type = '@@Mime_Type',
                                    CBB = '@@CBB',
                                    Mime_TypeLogo = '@@Mime_TypeLogo',
                                    LogoFile = '@@LogoFile',
                                    IsActiveFE = @@IsActiveFE,
                                    IsCompleteFE = @@IsCompleteFE,
                                    FactElection = @@FactElection,     
                                    Fecha_Mantto = NOW(),
                                    Usuario_Mantto = '@@Usuario_Mantto'

                                 WHERE Id_Datos_Factura = @@Id";

                Query = Query.Replace("@@RFC", this._RFC);
                Query = Query.Replace("@@Id_Regimen_Fiscal", Id_Regimen_Fiscal.ToString());
                Query = Query.Replace("@@Aprobacion_SICOFI", Aprobacion_SICOFI);
                Query = Query.Replace("@@Mime_Type", Mime_Type);
                Query = Query.Replace("@@CBB", CBB);
                Query = Query.Replace("@@Mime_TypeLogo", Mime_TypeLogo);
                Query = Query.Replace("@@LogoFile", LogoFile);
                Query = Query.Replace("@@FactElection", FactElection.ToString());

                if (IsActiveFE)
                    Query = Query.Replace("@@IsActiveFE", "1");
                else
                    Query = Query.Replace("@@IsActiveFE", "0");

                if (IsCompleteFE)
                    Query = Query.Replace("@@IsCompleteFE", "1");
                else
                    Query = Query.Replace("@@IsCompleteFE", "0");

                Query = Query.Replace("@@Usuario_Alta", this._Usuario);
                Query = Query.Replace("@@Usuario_Mantto", this._Usuario);

                Query = Query.Replace("@@Id", ((Int32)Id).ToString());

                return SrvDB.ExecuteQuery_Update(Query);                
                
            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }
        }

        #endregion

        #region DELETE

        public Respuesta Delete_Empresa_Datos_Factura_Por_Id(
            object Id
        )
        {
            try
            {
                string Query = @"DELETE FROM dbo_Cat_Empresa_Datos_Factura
                                    WHERE Id_Datos_Factura = @@Id";

                Query = Query.Replace("@@Id", ((Int32)Id).ToString());

                return SrvDB.ExecuteQuery_Delete(Query);
                
                
            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }
        }

        #endregion

        #region SELECT

        public DataTable Get_Empresa_Datos_Factura_Por_Id(object Id)
        {

            string Query = @"SELECT 
                                A1.*,
								A2.Nombre AS Desc_Regimen_Fiscal

                            FROM (dbo_Cat_Empresa_Datos_Factura AS A1
                            
                                INNER JOIN dbo_Cat_Regimen_Fiscal   AS A2 ON A1.Id_Regimen_Fiscal = A2.Id_Regimen_Fiscal)

                            WHERE A1.Id_Datos_Factura = @@Id";

            Query = Query.Replace("@@Id", ((Int32)Id).ToString());

            return SrvDB.ExecuteQuery_Get(Query);
        }       

        public DataTable Get_Empresa_Datos_Factura()
        {
            string Query = @"SELECT 
                                A1.*,
								A2.Nombre AS Desc_Regimen_Fiscal

                            FROM (dbo_Cat_Empresa_Datos_Factura AS A1
                            
                                INNER JOIN dbo_Cat_Regimen_Fiscal   AS A2 ON A1.Id_Regimen_Fiscal = A2.Id_Regimen_Fiscal)

                            WHERE A1.RFC = '@@RFC'";
            Query = Query.Replace("@@RFC", this._RFC);

            return SrvDB.ExecuteQuery_Get(Query);
        }

        #endregion
    }
}
